package com.motu.vertx.module.utility.webhandler;

import com.motu.vertx.module.utility.toolset.TimeUtils;
import com.motu.vertx.module.utility.webtool.BaseWebHandler;
import com.motu.vertx.module.utility.webtool.SearchCondition;

import java.util.ArrayList;
import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.motu.vertx.module.utility.postgresql.PostgreUtils;
import com.motu.vertx.module.utility.toolset.Tool;

import io.vertx.core.http.HttpServerResponse;
import io.vertx.core.json.JsonArray;
import io.vertx.core.json.JsonObject;
import io.vertx.ext.sql.SQLClient;
import io.vertx.ext.web.RoutingContext;
import io.vertx.ext.web.client.WebClient;
public class RecordlogHandler extends BaseWebHandler{
	private static final Logger logger = LoggerFactory.getLogger(RecordlogHandler.class);
	public static final String tableName = "record_";

	private static SQLClient jdbcClientLog = null;

	public static void init(SQLClient jdbcLog){
		jdbcClientLog = jdbcLog;
	}

	/**
	 * 消耗获得查询,分页和总数一起查询
	 * @param routingContext
	 * @param json
	 * @param adminName
	 */
	public static void search(RoutingContext routingContext,JsonObject json,String adminName,WebClient client){
//		routingContext.vertx().executeBlocking(ft->{
			String table = json.getString("table");
			//		String key = json.getString("key");
			String sql1 = " FROM "+tableName+table+" where  1=1 ";//api like ? or apiname like ? order by id desc ;"; 
			String str1 = "";
			JsonArray params1 = new JsonArray();
			//		boolean sign = false;
			String dbkey2[] = null;
			if(json.containsKey("type")){
				int type = json.getInteger("type");
				if(type == 1){

					dbkey2 =  new String[]{"consume"};
					if(!json.containsKey(dbkey2[0])) {
						str1 = "and consume > 0 ";
					}
				}
				else{
					dbkey2 =  new String[]{"income"};
					if(!json.containsKey(dbkey2[0])) {
						str1 = "and income > 0 ";
					}
				}
			}
			String dbkey1 = "";
			if(table.equalsIgnoreCase("item")){
				dbkey1 = "itemid";
			}else if(table.equalsIgnoreCase("value")){
				dbkey1 = "value_type";
			}
			List<SearchCondition> scList = new ArrayList<SearchCondition>();
			if (json.containsKey("stime")) {
				json.put("stime", TimeUtils.getTimeStrIntValue(json.getString("stime")));
			}
			if (json.containsKey("etime")) {
				json.put("etime", TimeUtils.getTimeStrIntValue(json.getString("etime")));
			}
			scList.add(new SearchCondition("stime", ">=", new String[]{"time"}, null));
			scList.add(new SearchCondition("etime", "<=", new String[]{"time"}, null));
			scList.add(new SearchCondition("logtype", "=", new String[]{"type"}, null));
			scList.add(new SearchCondition("itemid", "=", new String[]{dbkey1}, null));
			scList.add(new SearchCondition("minitemid", ">=", new String[]{"itemid"}, null));
			scList.add(new SearchCondition("maxitemid", "<=", new String[]{"itemid"}, null));
			scList.add(new SearchCondition("userid", "=", null, null));
			scList.add(new SearchCondition("serverid", "=", null, null));
			scList.add(new SearchCondition("value", "=", dbkey2, null));
			scList.add(new SearchCondition("minvalue", ">=", dbkey2, null));
			scList.add(new SearchCondition("maxvalue", "<=", dbkey2, null));
			scList.add(new SearchCondition("param", "like", null, null));
			sql1 += str1+getSearchString(scList, json, params1);

			String sql = "select count(1) "+sql1+";";
			String sql2 = "select * "+sql1+" order by time desc "+" limit ?,?; ";
			JsonArray params2 = params1.copy();
			int pageNo = json.getInteger("pageNo");
			int pageSize = json.getInteger("pageSize");
			int start = pageNo*pageSize;
			params2.add(start);
			params2.add(pageSize);
			searchFromTable(routingContext,jdbcClientLog, tableName, sql, params1, sql2, params2);
//		},true,r->{});
	}
	
	/**
	 * 消耗获得查询,只查询分页
	 * @param routingContext
	 * @param json
	 * @param adminName
	 */
	public static void search_limit(RoutingContext routingContext,JsonObject json,String adminName,WebClient client){
		HttpServerResponse response = routingContext.response();
		String table = json.getString("table");
		String sql1 = " FROM "+tableName+table+" where  1=1 ";
		if (table.equalsIgnoreCase("item") && json.containsKey("userid") && json.containsKey("param")) {  //强制使用索引
			sql1 = " FROM "+tableName+table+" force index(idx_userid_itemid_time)  where  1=1 ";
		}
		String str1 = "";
		JsonArray params1 = new JsonArray();
		String dbkey2[] = null;
		if(json.containsKey("type")){
			int type = json.getInteger("type");
			if(type == 1){
				dbkey2 =  new String[]{"consume"};
				if(!json.containsKey(dbkey2[0])) {
					str1 = "and consume > 0 ";
				}
			}
			else{
				dbkey2 =  new String[]{"income"};
				if(!json.containsKey(dbkey2[0])) {
					str1 = "and income > 0 ";
				}
			}
		}
		String dbkey1 = "";
		if(table.equalsIgnoreCase("item")){
			dbkey1 = "itemid";
		}else if(table.equalsIgnoreCase("value")){
			dbkey1 = "value_type";
		}
		List<SearchCondition> scList = new ArrayList<SearchCondition>();
		if (json.containsKey("stime")) {
			json.put("stime", TimeUtils.getTimeStrIntValue(json.getString("stime")));
		}
		if (json.containsKey("etime")) {
			json.put("etime", TimeUtils.getTimeStrIntValue(json.getString("etime")));
		}
		scList.add(new SearchCondition("stime", ">=", new String[]{"time"}, null));
		scList.add(new SearchCondition("etime", "<=", new String[]{"time"}, null));
		scList.add(new SearchCondition("logtype", "=", new String[]{"type"}, null));
		scList.add(new SearchCondition("itemid", "=", new String[]{dbkey1}, null));
		scList.add(new SearchCondition("minitemid", ">=", new String[]{"itemid"}, null));
		scList.add(new SearchCondition("maxitemid", "<=", new String[]{"itemid"}, null));
		scList.add(new SearchCondition("userid", "=", null, null));
		scList.add(new SearchCondition("serverid", "=", null, null));
		scList.add(new SearchCondition("value", "=", dbkey2, null));
		scList.add(new SearchCondition("minvalue", ">=", dbkey2, null));
		scList.add(new SearchCondition("maxvalue", "<=", dbkey2, null));
		scList.add(new SearchCondition("param", "like", null, null));
		sql1 += str1+getSearchString(scList, json, params1);

		String sql2 = "select * "+sql1+" order by time desc "+" limit ?,?; ";
		int pageNo = json.getInteger("pageNo");
		int pageSize = json.getInteger("pageSize");
		int start = pageNo*pageSize;
		params1.add(start);
		params1.add(pageSize);
		long startTime = System.currentTimeMillis();
		logger.info(Tool.getSqlString(sql2, params1));
		PostgreUtils.queryJsonArrayFromDb(jdbcClientLog, sql2, params1, handler->{
			if(handler.succeeded()) {
				int queryTime = (int)(System.currentTimeMillis()-startTime)/1000;
				JsonObject result = new JsonObject();
				result.put("rows", handler.result());
				sendHttpResponse(response, result);
				if(queryTime >= 10) {//大于10秒的查询，打印一下
					recordQueryLongTime(jdbcClientLog, sql2, params1, "", new JsonArray(), queryTime);
				}
			}else {
				sendHttpResponseError(response,  handler.cause().getMessage());
			}
		});
	}
	
	/**
	 * 消耗获得查询,只查询总条数
	 * @param routingContext
	 * @param json
	 * @param adminName
	 */
	public static void search_count(RoutingContext routingContext,JsonObject json,String adminName,WebClient client){
		HttpServerResponse response = routingContext.response();
		String table = json.getString("table");
		String sql1 = " FROM "+tableName+table+" where  1=1 ";
		if (table.equalsIgnoreCase("item") && json.containsKey("userid") && json.containsKey("param")) {  //强制使用索引
			sql1 = " FROM "+tableName+table+" force index(idx_userid_itemid_time) where  1=1 ";
		}
		String str1 = "";
		JsonArray params1 = new JsonArray();
		String dbkey2[] = null;
		if(json.containsKey("type")){
			int type = json.getInteger("type");
			if(type == 1){
				dbkey2 =  new String[]{"consume"};
				if(!json.containsKey(dbkey2[0])) {
					str1 = "and consume > 0 ";
				}
			}
			else{
				dbkey2 =  new String[]{"income"};
				if(!json.containsKey(dbkey2[0])) {
					str1 = "and income > 0 ";
				}
			}
		}
		String dbkey1 = "";
		if(table.equalsIgnoreCase("item")){
			dbkey1 = "itemid";
		}else if(table.equalsIgnoreCase("value")){
			dbkey1 = "value_type";
		}
		List<SearchCondition> scList = new ArrayList<SearchCondition>();
		if (json.containsKey("stime")) {
			json.put("stime", TimeUtils.getTimeStrIntValue(json.getString("stime")));
		}
		if (json.containsKey("etime")) {
			json.put("etime", TimeUtils.getTimeStrIntValue(json.getString("etime")));
		}
		scList.add(new SearchCondition("stime", ">=", new String[]{"time"}, null));
		scList.add(new SearchCondition("etime", "<=", new String[]{"time"}, null));
		scList.add(new SearchCondition("logtype", "=", new String[]{"type"}, null));
		scList.add(new SearchCondition("itemid", "=", new String[]{dbkey1}, null));
		scList.add(new SearchCondition("minitemid", ">=", new String[]{"itemid"}, null));
		scList.add(new SearchCondition("maxitemid", "<=", new String[]{"itemid"}, null));
		scList.add(new SearchCondition("userid", "=", null, null));
		scList.add(new SearchCondition("serverid", "=", null, null));
		scList.add(new SearchCondition("value", "=", dbkey2, null));
		scList.add(new SearchCondition("minvalue", ">=", dbkey2, null));
		scList.add(new SearchCondition("maxvalue", "<=", dbkey2, null));
		scList.add(new SearchCondition("param", "like", null, null));
		sql1 += str1+getSearchString(scList, json, params1);

		String sql = "select count(1) "+sql1+";";
		long startTime = System.currentTimeMillis();
		logger.info(Tool.getSqlString(sql, params1));
		PostgreUtils.queryJsonArrayFromDb(jdbcClientLog, sql, params1, handler->{
			if(handler.succeeded()) {
				int queryTime = (int)(System.currentTimeMillis()-startTime)/1000;
				JsonObject result = new JsonObject();
				int total = handler.result().getJsonObject(0).getInteger("count(1)");
				result.put("total", total);
				sendHttpResponse(response, result);
				if(queryTime >= 10) {//大于10秒的查询，打印一下
					recordQueryLongTime(jdbcClientLog, sql, params1, "", new JsonArray(), queryTime);
				}
			}else {
				sendHttpResponseError(response,  handler.cause().getMessage());
			}
		});
	}

	/**
	 * 统计消耗获得的总和
	 * @param routingContext
	 * @param json
	 * @param adminName
	 */
	public static void fetchtotal(RoutingContext routingContext,JsonObject json,String adminName,WebClient client){
		routingContext.vertx().executeBlocking(()->{
			HttpServerResponse response = routingContext.response();
			
//			JsonObject result = new JsonObject();
//			result.put("consumetotal", 0);
//			result.put("incometotal", 0);
//			sendHttpResponse(response, result);
			
			String table = json.getString("table");  	//确定查询哪个表record_diamond  record_gold等
			String sql1 = " FROM " + tableName + table +" where  1=1 ";//api like ? or apiname like ? order by id desc ;"; 
			if (table.equalsIgnoreCase("item") && json.containsKey("userid") && json.containsKey("param")) {  //强制使用索引
				sql1 = " FROM "+tableName+table+" force index(idx_userid_itemid_time) where  1=1 ";
			}
			String str1 = "";
			JsonArray params1 = new JsonArray();
			String dbkey2[] = null;
			if(json.containsKey("type")){
				int type = json.getInteger("type");
				if(type == 1){

					dbkey2 =  new String[]{"consume"};
					if(!json.containsKey(dbkey2[0])) {
						str1 = "and consume > 0 ";
					}
				}
				else{
					dbkey2 =  new String[]{"income"};
					if(!json.containsKey(dbkey2[0])) {
						str1 = "and income > 0 ";
					}
				}
			}
			String dbkey1 = "";
			if(table.equalsIgnoreCase("item")){
				dbkey1 = "itemid";
			}else if(table.equalsIgnoreCase("value")){
				dbkey1 = "value_type";
			}
			List<SearchCondition> scList = new ArrayList<SearchCondition>();
			if (json.containsKey("stime")) {
				json.put("stime", TimeUtils.getTimeStrIntValue(json.getString("stime")));
			}
			if (json.containsKey("etime")) {
				json.put("etime", TimeUtils.getTimeStrIntValue(json.getString("etime")));
			}
			scList.add(new SearchCondition("stime", ">=", new String[]{"time"}, null));
			scList.add(new SearchCondition("etime", "<=", new String[]{"time"}, null));
			scList.add(new SearchCondition("logtype", "=", new String[]{"type"}, null));
			scList.add(new SearchCondition("itemid", "=", new String[]{dbkey1}, null));
			scList.add(new SearchCondition("minitemid", ">=", new String[]{"itemid"}, null));
			scList.add(new SearchCondition("maxitemid", "<=", new String[]{"itemid"}, null));
			scList.add(new SearchCondition("serverid", "=", null, null));
			scList.add(new SearchCondition("userid", "=", null, null));
			scList.add(new SearchCondition("value", "=", dbkey2, null));
			scList.add(new SearchCondition("minvalue", ">=", dbkey2, null));
			scList.add(new SearchCondition("maxvalue", "<=", dbkey2, null));
			scList.add(new SearchCondition("param", "like", null, null));
			sql1 += str1+getSearchString(scList, json, params1);

			String sql = "select sum(consume) as consume, sum(income) as income " + sql1 + ";";
			long startTime = System.currentTimeMillis();
			logger.info("####### fetchtotal sql:"+sql+" params1:"+params1.toString());
			PostgreUtils.queryJsonArrayFromDb(jdbcClientLog, sql, params1, handler->{
				logger.info("####### fetchtotal complete time:"+(System.currentTimeMillis()-startTime)+"ms");
				if (handler.succeeded()) {
					JsonArray list = handler.result();
					JsonObject result = new JsonObject();
					if(list.size() > 0) {
						result.put("consumetotal", list.getJsonObject(0).getLong("consume"));
						result.put("incometotal", list.getJsonObject(0).getLong("income"));
					}else {
						result.put("consumetotal", 0);
						result.put("incometotal", 0);
					}
					sendHttpResponse(response, result);
				} else {
					sendHttpResponseError(response, handler.cause().getMessage());
				}
			});
			return null;
		},true,r->{});
	}

	/**
	 * 用户汇总查询
	 * @param routingContext
	 * @param json
	 * @param adminName
	 */
	public static void statuser(RoutingContext routingContext,JsonObject json,String adminName,WebClient client){
		routingContext.vertx().executeBlocking(()->{
			String table = json.getString("table");
			String sql1 = " FROM "+tableName+table+" where  1=1 ";//api like ? or apiname like ? order by id desc ;"; 
			String str1 = "";
			JsonArray params1 = new JsonArray();
			String str0 = "";
			int type = json.getInteger("type");
			if(type == 1){
				if(!json.containsKey("consume")) {
					str1 = "and consume > 0 ";
				}
				str0 = ",sum(consume) as count ";
			}
			else{
				if(!json.containsKey("income")) {
					str1 = "and income > 0 ";
				}
				str0 = ",sum(income) as count ";
			}
			String dbkey1 = "";
			if(table.equalsIgnoreCase("item")){
				dbkey1 = "itemid";
			}else if(table.equalsIgnoreCase("value")){
				dbkey1 = "value_type";
			}
			List<SearchCondition> scList = new ArrayList<SearchCondition>();
			if (json.containsKey("stime")) {
				json.put("stime", TimeUtils.getTimeStrIntValue(json.getString("stime")));
			}
			if (json.containsKey("etime")) {
				json.put("etime", TimeUtils.getTimeStrIntValue(json.getString("etime")));
			}
			scList.add(new SearchCondition("stime", ">=", new String[]{"time"}, null));
			scList.add(new SearchCondition("etime", "<=", new String[]{"time"}, null));
			scList.add(new SearchCondition("logtype", "=", new String[]{"type"}, null));
			scList.add(new SearchCondition("itemid", "=", new String[]{dbkey1}, null));
			scList.add(new SearchCondition("userid", "=", null, null));
			sql1 += str1+getSearchString(scList, json, params1);

			String sql = "select userid,max(level) as level,max(vip) as vip,type"+str0+sql1+" group by type order by count desc;";
			PostgreUtils.queryJsonArrayFromDb(jdbcClientLog, sql, params1, handler->{
				if(handler.succeeded()){
					JsonArray list = handler.result();
					JsonObject result = new JsonObject();
					result.put("rows",list);
					sendHttpResponse(routingContext.response(), result);
				}else{
					sendHttpResponseError(routingContext.response(), handler.cause().getMessage());
				}
			});
			return null;
		},true,r->{});
	}

	/**
	 * 数据库慢查询日志
	 * @param routingContext
	 * @param json
	 * @param adminName
	 */
	public static void log_query(RoutingContext routingContext, JsonObject json,String adminName,WebClient client) {
		List<SearchCondition> scList = new ArrayList<SearchCondition>();
		if (json.containsKey("stime")) {
			json.put("stime", TimeUtils.getTimeStrIntValue(json.getString("stime")));
		}
		if (json.containsKey("etime")) {
			json.put("etime", TimeUtils.getTimeStrIntValue(json.getString("etime")));
		}
		scList.add(new SearchCondition("querytime", ">=", null, null));
		scList.add(new SearchCondition("stime", ">=", new String[]{"addtime"}, null));
		scList.add(new SearchCondition("etime", "<=", new String[]{"addtime"}, null));
		scList.add(new SearchCondition("sql1_final", "like", null, null));
		searchFromTable(routingContext, jdbcClientLog, json, "log_query", scList);
	}

	/**
	 * 全服汇总查询
	 * @param routingContext
	 * @param json
	 * @param adminName
	 */
	public static void statserver(RoutingContext routingContext,JsonObject json,String adminName,WebClient client){
		routingContext.vertx().executeBlocking(()->{
			String table = json.getString("table");
			String sql1 = " FROM "+tableName+table+" where  1=1 ";//api like ? or apiname like ? order by id desc ;"; 
			String str1 = "";
			JsonArray params1 = new JsonArray();
			String str0 = "";
			String dbkey2[] = null;
			int type = json.getInteger("type");
			if(type == 1){
				dbkey2 =  new String[]{"consume"};
				if(!json.containsKey("consume")) {
					str1 = "and consume > 0 ";
				}
				str0 = ",sum(consume) as count ";
			}
			else{
				dbkey2 =  new String[]{"income"};
				if(!json.containsKey("income")) {
					str1 = "and income > 0 ";
				}
				str0 = ",sum(income) as count ";
			}
			String dbkey1 = "";
			if(table.equalsIgnoreCase("item")){
				dbkey1 = "itemid";
			}else if(table.equalsIgnoreCase("value")){
				dbkey1 = "value_type";
			}

			List<SearchCondition> scList = new ArrayList<SearchCondition>();
			if (json.containsKey("stime")) {
				json.put("stime", TimeUtils.getTimeStrIntValue(json.getString("stime")));
			}
			if (json.containsKey("etime")) {
				json.put("etime", TimeUtils.getTimeStrIntValue(json.getString("etime")));
			}
			scList.add(new SearchCondition("stime", ">=", new String[]{"time"}, null));
			scList.add(new SearchCondition("etime", "<=", new String[]{"time"}, null));
			scList.add(new SearchCondition("logtype", "=", new String[]{"type"}, null));
			scList.add(new SearchCondition("itemid", "=", new String[]{dbkey1}, null));
			scList.add(new SearchCondition("serverid", "=", null, null));
			scList.add(new SearchCondition("value", "=", dbkey2, null));
			scList.add(new SearchCondition("minvalue", ">=", dbkey2, null));
			scList.add(new SearchCondition("maxvalue", "<=", dbkey2, null));
			sql1 += str1+getSearchString(scList, json, params1);

			String sql = "select type,from_unixtime(`time`,'%Y-%m-%d') as date "+str0+sql1+" group by type,date desc;";
			PostgreUtils.queryJsonArrayFromDb(jdbcClientLog, sql, params1, handler->{
				if(handler.succeeded()){
					JsonArray list = handler.result();
					JsonObject result = new JsonObject();
					result.put("rows",list);
					sendHttpResponse(routingContext.response(), result);
				}else{
					sendHttpResponseError(routingContext.response(), handler.cause().getMessage());
				}
			});
			return null;
		},true,r->{});
	}

	//		private int getLogType() {
	//			public static int DIAMOND_CONSUME = 10000;//钻石消耗
	//			public static int DIAMOND_INCOME = 11000;//钻石获得
	//			public static int GOLD_CONSUME = 12000;//金币消耗
	//			public static int GOLD_INCOME = 13000;//金币获得
	//			public static int ITEM_CONSUME = 14000;//物品消耗
	//			public static int ITEM_INCOME = 15000;//物品获得
	//			public static int VALUE_CONSUME = 16000; //数值类型消耗
	//			public static int VALUE_INCOME = 17000;  //数值类型获得
	//		}

}
